package com.code.entity.generator;

import com.code.constant.BooleanEnum;
import com.code.constant.DataTypeEnum;
import com.code.constant.KeyWords;
import com.code.constant.SqlConstant;
import com.code.utils.GeneratorUtil;
import lombok.Data;
import org.apache.commons.lang3.StringUtils;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;


@Data
public class TableGenerator {
    private Table table;

    public String getMysqlCreateTableSql() throws Exception {
        setTableName();
        StringBuilder sql = new StringBuilder(SqlConstant.newLine);
        sql.append(getSqlSegment(SqlConstant.tableCreate_start_mysql, table, null))
                .append(SqlConstant.tableCreate_id_mysql);

        sql = getMysqlSqlContent(sql);
        if (BooleanEnum.YES.getExcelData().equals(table.getColumns().get(0).getWithHistory())) {
            sql.append(getMysqlCreateHistoryTableSql());
        }
        return sql.toString();
    }

    private boolean ifWithHistory() {
        return BooleanEnum.YES.getExcelData().equals(table.getColumns().get(0).getWithHistory())
                && table.getTableName().toLowerCase().endsWith(SqlConstant.history_table_name_suffix);
    }

    public String getMysqlCreateHistoryTableSql() throws Exception {
        setHistoryTableName();
        StringBuilder sql = new StringBuilder(SqlConstant.newLine);
        sql.append(getSqlSegment(SqlConstant.tableCreate_start_mysql, table, null));
        if (!getColumnNamesLowerCase(table).contains(SqlConstant.history_table_index_tradeDate) && createHistoryDate(table)) {
            sql.append(SqlConstant.tableCreate_history_tradeDate_mysql);
        }
        sql.append(SqlConstant.tableCreate_id_mysql);

        return getMysqlSqlContent(sql).toString();
    }

    private StringBuilder getMysqlSqlContent(StringBuilder sql) throws Exception {
        List<Column> addedColumns = new ArrayList<>();
        for (Column data : table.getColumns()) {
            //跳过主键 与 空白行
            if (data.getColumnName() == null || data.getColumnName().isEmpty()
                    || data.getColumnName().toLowerCase().equals(SqlConstant.id_column_name)) {
                continue;
            }
            //处理特殊字段名称
            if (Arrays.asList(SqlConstant.strangeNames).contains(data.getColumnName())) {
                data.setColumnName("`" + data.getColumnName() + "`");
            }
            sql.append(getSqlSegment(SqlConstant.tableCreate_column_mysql_begin, null, data));
            if (StringUtils.isNotBlank(data.getDataLength()) && !data.getDataLength().trim().equals("0")) {
                sql.append(getSqlSegment(SqlConstant.column_length, null, data));
            }
            if (StringUtils.isNotBlank(data.getDefaultValue())) {
                sql.append(getSqlSegment(SqlConstant.tableCreate_column_mysql_default, null, data));
            }
            //是否区分大小写
            if (StringUtils.isNotBlank(data.getCareLowCase()) && data.getCareLowCase().equals("是")) {
                sql.append(getSqlSegment(SqlConstant.tableCreate_column_mysql_binary_end, null, data));
            } else {
                sql.append(getSqlSegment(SqlConstant.tableCreate_column_mysql_end, null, data));
            }
            if (data.getExtend()) {
                addedColumns.add(data);
            }
        }
        sql.append(SqlConstant.tableCreate_key_mysql);
        //创建多索引
        if (table.getUniqueIndexGroup().size() > 0) {
            for (Map.Entry<Integer, List<Column>> columnData : table.getUniqueIndexGroup().entrySet()) {
                sql.append(getIndexSqlSegment(SqlConstant.tableCreate_unique_mysql, table,
                        String.valueOf(columnData.getKey()), columnData.getValue()));
            }
        }
        if (table.getIndexGroup().size() > 0) {
            for (Map.Entry<String, List<Column>> columnData : table.getIndexGroup().entrySet()) {
                sql.append(getIndexSqlSegment(SqlConstant.tableCreate_index_mysql, table, columnData.getKey(), columnData.getValue()));
            }
        }
        sql.append(getSqlSegment(SqlConstant.tableCreate_end_mysql, table, null));
        //新增列
        for (Column data : addedColumns) {
            sql.append(getSqlSegment(SqlConstant.add_column_proc_mysql_begin, null, data));
            if (StringUtils.isNotBlank(data.getDataLength()) && !data.getDataLength().trim().equals("0")) {
                sql.append(getSqlSegment(SqlConstant.column_length, null, data));
            }
            if (StringUtils.isNotBlank(data.getDefaultValue())) {
                sql.append(getSqlSegment(SqlConstant.tableCreate_column_mysql_default, null, data));
            }
            if (StringUtils.isNotBlank(data.getCareLowCase()) && data.getCareLowCase().equals("是")) {
                sql.append(getSqlSegment(SqlConstant.add_column_proc_mysql_modify_binary, null, data));
            } else {
                sql.append(getSqlSegment(SqlConstant.add_column_proc_mysql_modify, null, data));
            }

            if (StringUtils.isNotBlank(data.getDataLength()) && !data.getDataLength().trim().equals("0")) {
                sql.append(getSqlSegment(SqlConstant.column_length, null, data));
            }
            if (StringUtils.isNotBlank(data.getDefaultValue())) {
                sql.append(getSqlSegment(SqlConstant.tableCreate_column_mysql_default, null, data));
            }

            if (StringUtils.isNotBlank(data.getCareLowCase()) && data.getCareLowCase().equals("是")) {
                sql.append(getSqlSegment(SqlConstant.add_column_proc_mysql_binary_end, null, data));
            } else {
                sql.append(getSqlSegment(SqlConstant.add_column_proc_mysql_end, null, data));
            }

        }
        if (table.getUniqueIndexGroup().size() > 0) {
            for (Map.Entry<Integer, List<Column>> columnData : table.getUniqueIndexGroup().entrySet()) {
                if (ifIndexNeedExtend(String.valueOf(columnData.getKey()))) {
                    sql.append(getIndexSqlSegment(SqlConstant.add_unique_column_mysql, table,
                            String.valueOf(columnData.getKey()), columnData.getValue()));
                }
            }
        }
        if (table.getIndexGroup().size() > 0) {
            for (Map.Entry<String, List<Column>> columnData : table.getIndexGroup().entrySet()) {
                if (ifIndexNeedExtend(columnData.getKey())) {
                    sql.append(getIndexSqlSegment(SqlConstant.add_index_column_mysql, table, columnData.getKey(), columnData.getValue()));
                }
            }
        }
        return sql;
    }

    private boolean ifIndexNeedExtend(String index) {
        for (Column c : table.getColumns()) {
            if (BooleanEnum.YES.getExcelData().equals(c.getExtendFlag()) && StringUtils.isNotBlank(c.getIndex())
                    && (c.getIndex().contains(index) || (index.endsWith("0") && BooleanEnum.YES.getExcelData().equals(c.getIndex())))) {
                return true;
            }
        }
        return false;
    }

    public String getOracleCreateTableSql() throws Exception {
        setTableName();
        StringBuilder sql = new StringBuilder(SqlConstant.newLine);
        if (table.getTableName().length() > 30) {
            System.out.println("表名:" + table.getTableName() + "长度超出最大长度30，不生成脚本");
            return sql.toString();
        }
        sql.append(getSqlSegment(SqlConstant.tableDeclare_start_oracle, table, null))
                .append(getSqlSegment(SqlConstant.tableCreate_start_oracle, table, null))
                .append(SqlConstant.tableCreate_id_oracle);

        sql = getOracleSqlContent(sql);
        if (BooleanEnum.YES.getExcelData().equals(table.getColumns().get(0).getWithHistory())) {
            sql.append(getOracleCreateHistoryTableSql());
        }
        return sql.toString();
    }

    private void setHistoryTableName() {
        String hisTableName = table.getTableName() + SqlConstant.history_table_name_suffix;
        table.setTableName(hisTableName);
        for (Column column : table.getColumns()) {
            column.setTableName(hisTableName);
        }
    }

    private void setTableName() {
        if (table.getTableName().endsWith(SqlConstant.history_table_name_suffix)) {
            String tableName = table.getTableName().substring(0, table.getTableName().length() - 4);
            table.setTableName(tableName);
            for (Column column : table.getColumns()) {
                column.setTableName(tableName);
            }
        }
    }

    public String getOracleCreateHistoryTableSql() throws Exception {
        setHistoryTableName();
        StringBuilder sql = new StringBuilder(SqlConstant.newLine);
        if (table.getTableName().length() > 30) {
            System.out.println("表名:" + table.getTableName() + "长度超出最大长度30，不生成脚本");
            return sql.toString();
        }
        sql.append(getSqlSegment(SqlConstant.tableDeclare_start_oracle, table, null))
                .append(getSqlSegment(SqlConstant.tableCreate_start_oracle, table, null));
        if (!getColumnNamesLowerCase(table).contains(SqlConstant.history_table_index_tradeDate) && createHistoryDate(table)) {
            sql.append(SqlConstant.tableCreate_history_tradeDate_oracle);
        }
        sql.append(SqlConstant.tableCreate_id_oracle);

        return getOracleSqlContent(sql).toString();
    }

    private StringBuilder getOracleSqlContent(StringBuilder sql) throws Exception {
        List<Column> addedColumns = new ArrayList<>();
        List<Column> triggeredColumns = new ArrayList<>();
        for (Column data : table.getColumns()) {
            //跳过主键 与 空白行
            if (data.getColumnName() == null || data.getColumnName().isEmpty()
                    || data.getColumnName().toLowerCase().equals(SqlConstant.id_column_name)) {
                continue;
            }
            Optional<DataTypeEnum> type = DataTypeEnum.getOracleTypeByMysqlType(data.getDataType());
            type.ifPresent(dataTypeEnum -> data.setDataType(dataTypeEnum.getOracleType()));
            sql.append(getSqlSegment(SqlConstant.tableCreate_column_oracle_begin, null, data, true));
            if (StringUtils.isNotBlank(data.getDataLength()) && !data.getDataLength().trim().equals("0")) {
                sql.append(getSqlSegment(SqlConstant.column_length, null, data));
            }
            if (StringUtils.isNotBlank(data.getDefaultValue())) {
                sql.append(getSqlSegment(SqlConstant.tableCreate_column_oracle_default, null, data, true));
            }
            sql.append(getSqlSegment(SqlConstant.tableCreate_column_oracle_end, null, data, true));
            if (data.getExtend()) {
                addedColumns.add(data);
            }
            if (data.getDefaultValue().toUpperCase().equals(KeyWords.mysqlDefaultDateUpdate)) {
                triggeredColumns.add(data);
            }
        }
        sql.append(SqlConstant.tableCreate_key_oracle)
                .append(SqlConstant.tableCreate_end_oracle);
        //创建多索引
        if (table.getUniqueIndexGroup().size() > 0) {
            for (Map.Entry<Integer, List<Column>> columnData : table.getUniqueIndexGroup().entrySet()) {
                sql.append(getIndexSqlSegment(SqlConstant.tableDeclare_init_unique_oracle, table,
                        String.valueOf(columnData.getKey()), columnData.getValue(), true));
            }
        }
        if (table.getIndexGroup().size() > 0) {
            for (Map.Entry<String, List<Column>> columnData : table.getIndexGroup().entrySet()) {
                sql.append(getIndexSqlSegment(SqlConstant.tableDeclare_init_index_oracle, table, columnData.getKey(), columnData.getValue(), true));
            }
        }
        sql.append(getSqlSegment(SqlConstant.tableDeclare_end_oracle, table, null, true))
                .append(getSqlSegment(SqlConstant.tableCreate_table_comment_oracle, table, null, true));
        //新增列
        for (Column data : addedColumns) {
            sql.append(getSqlSegment(SqlConstant.add_column_proc_oracle_begin, null, data));
            if (StringUtils.isNotBlank(data.getDataLength()) && !data.getDataLength().trim().equals("0")) {
                sql.append(getSqlSegment(SqlConstant.column_length, null, data));
            }
            if (StringUtils.isNotBlank(data.getDefaultValue())) {
                sql.append(getSqlSegment(SqlConstant.tableCreate_column_oracle_default, null, data, true));
            }
            sql.append(getSqlSegment(SqlConstant.add_column_proc_oracle_modify1, null, data, true));
            sql.append(getSqlSegment(SqlConstant.add_column_proc_oracle_modify2, null, data, true));
            if (StringUtils.isNotBlank(data.getDataLength()) && !data.getDataLength().trim().equals("0")) {
                sql.append(getSqlSegment(SqlConstant.column_length, null, data));
            }
            if (StringUtils.isNotBlank(data.getDefaultValue())) {
                sql.append(getSqlSegment(SqlConstant.tableCreate_column_oracle_default, null, data, true));
            }
            sql.append(getSqlSegment(SqlConstant.add_column_proc_oracle_modify3, null, data, true));
            sql.append(getSqlSegment(SqlConstant.add_column_proc_oracle_modify4, null, data, true));
            sql.append(getSqlSegment(SqlConstant.add_column_proc_oracle_end, null, data));
        }

        //需要建立trigger的列
        for (Column data : triggeredColumns) {
            sql.append(getSqlSegment(SqlConstant.tableCreate_table_trigger_oracle, null, data, true));
        }
        //列注释
        for (Column data : table.getColumns()) {
            if (data.getColumnName() == null || data.getColumnName().isEmpty()) {
                continue;
            }
            if (table.getTableName().toLowerCase().endsWith(SqlConstant.history_table_name_suffix)) {
                data.setTableName(table.getTableName());
            }
            sql.append(getSqlSegment(SqlConstant.tableCreate_column_comment_oracle, null, data, true));
        }
        //新索引
        if (table.getUniqueIndexGroup().size() > 0) {
            for (Map.Entry<Integer, List<Column>> columnData : table.getUniqueIndexGroup().entrySet()) {
                if (ifIndexNeedExtend(String.valueOf(columnData.getKey()))) {
                    sql.append(getIndexSqlSegment(SqlConstant.add_unique_column_oracle, table,
                            String.valueOf(columnData.getKey()), columnData.getValue(), true));
                }
            }
        }
        if (table.getIndexGroup().size() > 0) {
            for (Map.Entry<String, List<Column>> columnData : table.getIndexGroup().entrySet()) {
                if (ifIndexNeedExtend(columnData.getKey())) {
                    sql.append(getIndexSqlSegment(SqlConstant.add_index_column_oracle, table, columnData.getKey(), columnData.getValue()));
                }
            }
        }
        return sql;
    }

    String getTemplateResult(String source, Map<String, String> params) throws Exception {
        String result = GeneratorUtil.getTemplateResult(source, params);
        if (result.contains(SqlConstant.drop_index_oracle_prefix)) {
            String tempStr = result.substring(result.indexOf(SqlConstant.drop_index_oracle_prefix), result.indexOf("';"));
            result = result.replace(tempStr, tempStr.toUpperCase());
        }
        return result;
    }

    String getIndexSqlSegment(String template, Table table, String index, List<Column> columnList, Boolean isOracle) throws Exception {
        Map<String, String> params = new HashMap<>();
        //通过index区分多索引
        params.put(SqlConstant.tableName, table.getTableName());
        params.put(SqlConstant.tableComment, table.getTableComment());
        String indexTableName = table.getTableName() + index;
        if (isOracle) {
            if (indexTableName.length() > 26) {
                indexTableName = indexTableName.substring(indexTableName.length() - 26);
            }
        }
        params.put(SqlConstant.indexTableName, indexTableName);
        StringBuilder columns = new StringBuilder();
        for (Column data : columnList) {
            String columnName = data.getColumnName();
            if (isOracle) {
                if (columnName.startsWith("`") && columnName.endsWith("`")) {
                    //mysql关键字加了特殊处理，需要去除
                    columnName = columnName.trim().substring(1, columnName.lastIndexOf("`"));
                }
                if (KeyWords.oracleKeyWordsList.contains(columnName.toUpperCase())) {
                    //关键字的列，后面拼一个_
                    columnName = columnName + "_";
                }
            }
            columns.append(columnName).append(",");
        }
        if (ifWithHistory() && !getColumnNamesLowerCase(table).contains(SqlConstant.history_table_index_tradeDate) && createHistoryDate(table)) {
            //补充历史表索引
            columns.append(SqlConstant.history_table_index_tradeDate);
            params.put(SqlConstant.indexColumns, columns.toString());
        } else {
            params.put(SqlConstant.indexColumns, columns.substring(0, columns.toString().length() - 1));
        }

        return getTemplateResult(template, params);
    }

    String getIndexSqlSegment(String template, Table table, String index, List<Column> columnList) throws Exception {
        return getIndexSqlSegment(template, table, index, columnList, false);
    }

    String getSqlSegment(String template, Table table, Column column) throws Exception {
        return getSqlSegment(template, table, column, false);
    }

    String getSqlSegment(String template, Table table, Column column, boolean isOracle) throws Exception {
        Map<String, String> params = new HashMap<>();
        if (column != null) {
            String columnName = column.getColumnName();
            String defaultValue = column.getDefaultValue();
            String dataLength = column.getDataLength();
            if (isOracle) {
                if (columnName.startsWith("`") && columnName.endsWith("`")) {
                    //mysql关键字加了特殊处理，需要去除
                    columnName = columnName.trim().substring(1, columnName.lastIndexOf("`"));
                }
                if (KeyWords.oracleKeyWordsList.contains(columnName.toUpperCase())) {
                    //关键字的列，后面拼一个_
                    columnName = columnName + "_";
                }
                //默认值的单引号替换成双引号
                defaultValue = defaultValue.replaceAll("'", "''");

                if (defaultValue.toUpperCase().equals(KeyWords.mysqlDefaultDate)
                        || defaultValue.toUpperCase().equals(KeyWords.mysqlDefaultDateUpdate)) {
                    //时间戳默认值
                    defaultValue = KeyWords.oracleDefaultDate;
                }
                String oracleColumnTriggerName = column.getTableName() + "_" + column.getColumnName() + "_trg";
                if (oracleColumnTriggerName.length() > 30) {
                    //trigger超长最大长度限制
                    oracleColumnTriggerName = oracleColumnTriggerName
                            .substring(oracleColumnTriggerName.length() - 30);
                    if (oracleColumnTriggerName.startsWith("_")) {
                        oracleColumnTriggerName = oracleColumnTriggerName.substring(1);
                    }
                    oracleColumnTriggerName = GeneratorUtil.getStrWithoutPrefix(oracleColumnTriggerName);
                }
                params.put(SqlConstant.oracleColumnTriggerName, oracleColumnTriggerName);

                if (StringUtils.isNotEmpty(dataLength) && dataLength.indexOf(",") < 0
                        && Integer.valueOf(dataLength) > 4000) {
                    dataLength = String.valueOf(4000);
                }
            }
            if (table == null) {
                params.put(SqlConstant.columnName, columnName);
                params.put(SqlConstant.columnDefault, defaultValue);
                params.put(SqlConstant.dataLength, dataLength);
            }
        }
        if (table == null) {
            params.put(SqlConstant.tableName, column.getTableName());
            params.put(SqlConstant.dataType, column.getDataType());
            params.put(SqlConstant.nullState, column.getNullState());
            params.put(SqlConstant.columnComment, column.getColumnComment());
        } else {
            if (isOracle) {
                String oracleTriggerName = table.getTableName() + "_trg";
                if (oracleTriggerName.length() > 30) {
                    //trigger超长最大长度限制
                    oracleTriggerName = oracleTriggerName.substring(oracleTriggerName.length() - 30);
                    if (oracleTriggerName.startsWith("_")) {
                        oracleTriggerName = oracleTriggerName.substring(1);
                    }
                    oracleTriggerName = GeneratorUtil.getStrWithoutPrefix(oracleTriggerName);
                }
                String oracleSequenceName = table.getTableName() + "_id_seq";
                if (oracleSequenceName.length() > 30) {
                    //sequence超长最大长度限制
                    oracleSequenceName = oracleSequenceName.substring(oracleSequenceName.length() - 30);
                    if (oracleSequenceName.startsWith("_")) {
                        oracleSequenceName = oracleSequenceName.substring(1);
                    }
                    oracleSequenceName = GeneratorUtil.getStrWithoutPrefix(oracleSequenceName);
                }
                params.put(SqlConstant.oracleSequenceName, oracleSequenceName);
                params.put(SqlConstant.oracleTriggerName, oracleTriggerName);
            }
            params.put(SqlConstant.tableName, table.getTableName());
            params.put(SqlConstant.tableComment, table.getTableComment());
        }
        return getTemplateResult(template, params);
    }

    private List<String> getColumnNamesLowerCase(Table table) {
        List<String> columnNames = new ArrayList<>();
        for (Column column : table.getColumns()) {
            if (StringUtils.isNotBlank(column.getColumnName())) {
                columnNames.add(column.getColumnName().toLowerCase());
            }
        }
        return columnNames;
    }

    private boolean createHistoryDate(Table table) {
        for (Column column : table.getColumns()) {
            if (BooleanEnum.YES.getExcelData().equals(column.getWithHisDate())) {
                return true;
            }
        }
        return false;
    }
}
